package www.lagou.dao;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import www.lagou.entity.Account;
import www.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @author sjp
 * @date 2021/5/9 0:29
 * @description
 */
public class AccountDao {
    /**
     * 转账方法
     *
     * @param sourceCard 转出账号
     * @param targetCard 转入账号
     * @param amount     金额
     * @return 0表示不成功 1表示成功
     */
    public int transaction(String sourceCard, String targetCard, double amount) {
        Connection connection = null;
        try {
            //1.创建QueryRunner对象 手动管理方式
            QueryRunner runner = new QueryRunner();

            connection = DruidUtils.getConnection();
            //2.设置关闭自动提交事务
            connection.setAutoCommit(false);

            //3.执行业务
            //第一步：转出卡号sourceCard 减少amount余额
            double balance = runner.query(connection, "select balance from account where card = ?;", new ScalarHandler<Double>(), sourceCard);

            //判断不足转账金额（amount）
            if (balance < amount) {
                System.out.println("余额不足！");
                return 0;
            }

            // sourceCard账号-amount
            String sql1 = "update account set balance = balance - ? where card = ?;";
            Object[] updateParams1 = {amount, sourceCard};
            runner.update(connection, sql1, updateParams1);

            //第二步：转入卡号targetCard 增加amount余额
            // 55443332211账号+5000
            String sql2 = "update account set balance = balance + ? where card = ?;";
            Object[] updateParams2 = {amount, targetCard};
            runner.update(connection, sql2, updateParams2);

            //第三步：增加转入、转出记录
            //转出记录
            String sql3 = "insert into transaction values(?,?,?,?,?)";
            Object[] params1 = {null, sourceCard, "转出", amount, LocalDateTime.now()};
            runner.update(connection, sql3, params1);

            //转入记录
            Object[] params2 = {null, targetCard, "转入", amount, LocalDateTime.now()};
            runner.update(connection, sql3, params2);

            //4.提交事务
            connection.commit();

            System.out.println("转账成功！");
            return 1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("转账失败！");
            try {
                if (null != connection) {
                    connection.rollback();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        } finally {
            //5.关闭资源
            if (null != connection) {
                DbUtils.closeQuietly(connection);
            }
        }
    }

    /**
     * 显示数据库余额信息
     *
     * @throws SQLException
     */
    public void showBalanceInfo() throws SQLException {
        //1.创建QueryRunner对象 自动管理方式
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

        //将账户信息打印出来
        String queryAccountInfo = "select * from account";
        List<Account> query = runner.query(queryAccountInfo, new BeanListHandler<Account>(Account.class));
        for (Account account : query) {
            System.out.println("账户:" + account.getCard() + ",用户名：" + account.getUsername() + ",余额：" + account.getBalance());
        }
    }
}
